#!/bin/bash

APP=online_edu

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi


dim_user_zip="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dim_user_zip partition(dt)
select
    id,
    login_name,
    nick_name,
    passwd,
    real_name,
    phone_num,
    email,
    head_img,
    user_level,
    birthday,
    gender,
    create_time,
    operate_time,
    status,
    start_date,
    if(rn=2,date_sub('$do_date',1),end_date) end_date,
    if(rn=1,'9999-12-31',date_sub('$do_date',1)) dt
from
(
    select 
        id,
        login_name,
        nick_name,
        passwd,
        real_name,
        phone_num,
        email,
        head_img,
        user_level,
        birthday,
        gender,
        create_time,
        operate_time,
        status,
        start_date,
        end_date,
        row_number() over (partition by id order by start_date desc) rn
    from 
    (
        select
            id,
            login_name,
            nick_name,
            passwd,
            real_name,
            phone_num,
            email,
            head_img,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            status,
            start_date,
            end_date
        from ${APP}.dim_user_zip
        where dt='9999-12-31'
        union 
        select 
            id,
            login_name,
            nick_name,
            passwd,
            real_name,
            md5(if(phone_num 
                regexp '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\d{8}$',
                phone_num,null)) phone_num,
            md5(if(email 
                regexp '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\\.[a-zA-Z0-9_-]+)+$',
                email,null)) email,
            head_img,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            status,
            '$do_date' start_date,
            '9999-12-31' end_date
        from 
        (
            select
                data.id,
                data.login_name,
                data.nick_name,
                data.passwd,
                data.real_name,
                data.phone_num,
                data.email,
                data.head_img,
                data.user_level,
                data.birthday,
                data.gender,
                data.create_time,
                data.operate_time,
                data.status,
                row_number() over (partition by data.id order by ts desc) rn

            from ${APP}.ods_user_info_inc
            where dt='$do_date'
            )t1
        where rn=1
    )t2
)t3;
"

dim_course_subject_category_full="
with course as (
    select
    id,
    course_name,
    course_slogan,
    course_cover_url,
    subject_id,
    teacher,
    publisher_id,
    chapter_num,
    origin_price,
    reduce_amount,
    actul_price,
    course_introduce,
    create_time,
    update_time,
    deleted
    from ${APP}.ods_course_info_full
    where dt = '$do_date'

),
     subject as (
         select
         id,
         subject_name,
        category_id,
        creat_time
        from
         ${APP}.ods_base_subject_info_full
         where dt = '$do_date'
     ),
     category as (
         select
         id,
        category_name,
        create_time
         from
         ${APP}.ods_base_category_info_full
         where dt = '$do_date'
     )
insert overwrite table  ${APP}.dim_course_subject_category_full partition (dt = '$do_date')
    select
    category.id,
    category.category_name,
    category.create_time,
    subject.id,
    subject.subject_name,
    subject.creat_time,
    course.id,
    course.course_name,
    course.course_slogan,
    course.course_cover_url,
    course.teacher,
    course.publisher_id,
    course.chapter_num,
    course.origin_price,
    course.reduce_amount,
    course.actul_price,
    course.course_introduce,
    course.create_time,
    course.update_time,
    course.deleted
from course
left join subject on course.subject_id = subject.id
left join category on subject.category_id = category.id;
"

dim_chapter_full="
insert overwrite table ${APP}.dim_chapter_full partition(dt='$do_date')
select
    id,
    chapter_name,
    course_id,
       video_id,
       publisher_id,
       is_free,
       create_time,
       update_time,
       deleted

from ${APP}.ods_chapter_info_full
where dt ='$do_date';
"

dim_video_full="
insert overwrite table ${APP}.dim_video_full partition(dt = '$do_date')
select
       id,
       video_name,
       during_sec,
       video_status,
       video_size,
       video_url,
       video_source_id,
       version_id,
       chapter_id,
       course_id,
       publisher_id,
       create_time,
       update_time,
       deleted
from ${APP}.ods_video_info_full
where dt = '$do_date';
"
dim_test_full="
insert overwrite table ${APP}.dim_test_full partition(dt='$do_date')
select
   id, 
   paper_title, 
   course_id, 
   create_time,
   update_time, 
   publisher_id,
   deleted
from ${APP}.ods_test_paper_full
where dt = '$do_date';
"
dim_question_full="
with info as (
    select
    id,
    question_txt,
    chapter_id,
    course_id,
    question_type,
    create_time,
    update_time,
    publisher_id,
    deleted
    from ${APP}.ods_test_question_info_full
    where dt = '$do_date'
), option as (
    select
    id,
    option_txt,
    question_id,
    is_correct,
    create_time
    from ${APP}.ods_test_question_option_full
    where dt = '$do_date'
)
insert overwrite table ${APP}.dim_question_full partition (dt = '$do_date')
select
  option.question_id,
  info.question_txt,
  info.chapter_id,
  info.course_id,
  info.question_type,
  info.create_time,
  option.id,
   option.option_txt,
  option.is_correct,
  option.create_time,
  info.update_time,
  info.publisher_id,
  info.deleted
from info
left join option on info.id = option.question_id;
"
dim_point_full="
insert overwrite table ${APP}.dim_point_full partition(dt='$do_date')
select
 id,
 point_txt,
 point_level,
 course_id,
 chapter_id,
 k.publisher_id,
 k.create_time,
 k.update_time,
 k.deleted,
 q.question_id,
 q.create_time,
 q.publisher_id,
 q.deleted
from
(
 select
  id,
  point_txt,
  point_level,
  course_id,
  chapter_id,
  publisher_id,
  create_time,
  update_time,
  deleted
 from ${APP}.ods_knowledge_point_full
 where dt = '$do_date'
) k
left join
(
 select
  point_id,
  question_id,
  create_time,
  publisher_id,
  deleted
 from ${APP}.ods_test_point_question_full
 where dt = '$do_date'
) q
on k.id = q.point_id;
"

case $1 in
"dim_user_zip")
    hive -e "$dim_user_zip"
;;
"dim_course_subject_category_full")
    hive -e "$dim_course_subject_category_full"
;;
"dim_chapter_full")
    hive -e "$dim_chapter_full"
;;
"dim_video_full")
    hive -e "$dim_video_full"
;;
"dim_test_full")
    hive -e "$dim_test_full"
;;
"dim_question_full")
    hive -e "$dim_question_full"
;;
"dim_point_full")
    hive -e "$dim_point_full"
;;
"all")
    hive -e "$dim_user_zip$dim_course_subject_category_full$dim_chapter_full$dim_video_full$dim_test_full$dim_question_full$dim_point_full"
;;
esac

